import logging
import time
from typing import List, Dict, Optional, Tuple
from db.MySqlDB import MySQLDB


class SearchRecordService:
    """用户搜索记录服务类"""

    def __init__(self):
        self.mysqlDB = MySQLDB()
        self.table_name = "t_search_record"

    def add_search_record(self, keyword: str, count: int, user_id: int) -> Dict:
        """
        添加用户搜索记录
        """
        try:
            logging.info(f"添加搜索记录: user_id={user_id}, keyword={keyword}")

            # 检查参数有效性
            if not keyword or not user_id:
                return {"code": 400, "message": "关键词和用户ID不能为空", "data": None}

            if count < 0:
                return {"code": 400, "message": "搜索结果条数不能为负数", "data": None}
            record_id = self._generate_id()
            print("record_id=",record_id)
            # 插入搜索记录
            sql = f"""
                INSERT INTO {self.table_name} 
                (id,keyword, count, user_id) 
                VALUES (%s,%s, %s, %s)
            """
            params = (record_id,keyword.strip(), count, user_id)

            result = self.mysqlDB.execute_update(sql, params)

            if result:
                logging.info(f"搜索记录添加成功: user_id={user_id}, keyword={keyword}")
                return {"code": 200,"id": record_id}

            else:
                return {"code": 500, "message": "搜索记录添加失败", "data": None}

        except Exception as e:
            logging.error(f"添加搜索记录失败: {str(e)}")
            return {"code": 500, "message": f"添加搜索记录失败: {str(e)}", "data": None}

    def _generate_id(self) -> int:
        """生成用户ID"""
        return int(time.time() * 1000)

    def update_search_record(self, count: int, record_id: int) -> bool:
        print(f"count={count}, record_id={record_id}")

        try:
            logging.info(f"修改搜索记录: record_id={record_id}")
            if count < 0:
                return False

            # 插入搜索记录
            sql = f"""
                UPDATE {self.table_name}
            SET count = %s WHERE id = %s
            """
            params = ( count, record_id)
            result = self.mysqlDB.execute_update(sql, params)
            return True if result else False

        except Exception as e:
            logging.error(f"修改搜索记录失败: {str(e)}")
            return {"code": 500, "message": f"添加搜索记录失败: {str(e)}", "data": None}

    def get_user_search_records(self, user_id: int, limit: int = 20,
                                start_time: str = None, end_time: str = None) -> Dict:
        """
        获取用户的搜索记录
        Args:
            user_id: 用户ID
            limit: 返回记录数量限制
            start_time: 开始时间 (格式: YYYY-MM-DD HH:MM:SS)
            end_time: 结束时间 (格式: YYYY-MM-DD HH:MM:SS)
        Returns:
            搜索记录列表
        """
        try:
            logging.info(f"获取用户搜索记录: user_id={user_id}")

            # 构建查询条件
            where_conditions = ["user_id = %s"]
            params = [user_id]

            if start_time:
                where_conditions.append("create_time >= %s")
                params.append(start_time)

            if end_time:
                where_conditions.append("create_time <= %s")
                params.append(end_time)

            where_clause = " AND ".join(where_conditions)

            sql = f"""
                SELECT id, keyword, count, user_id, 
                       create_time, update_time
                FROM {self.table_name} 
                WHERE {where_clause}
                ORDER BY create_time DESC 
                LIMIT %s
            """
            params.append(limit)

            results = self.mysqlDB.execute_query(sql, tuple(params))

            records = []
            for record in results:
                records.append({
                    "id": record['id'],
                    "keyword": record['keyword'],
                    "count": record['count'],
                    "user_id": record['user_id'],
                    "create_time": record['create_time'].strftime("%Y-%m-%d %H:%M:%S") if record[
                        'create_time'] else None,
                    "update_time": record['update_time'].strftime("%Y-%m-%d %H:%M:%S") if record[
                        'update_time'] else None
                })

            return {
                "code": 200,
                "message": "获取搜索记录成功",
                "data": records
            }

        except Exception as e:
            logging.error(f"获取用户搜索记录失败: {str(e)}")
            return {"code": 500, "message": f"获取搜索记录失败: {str(e)}", "data": None}

    def get_popular_keywords(self, limit: int = 10, days: int = 30) -> Dict:
        """
        获取热门搜索关键词
        Args:
            limit: 返回关键词数量
            days: 统计天数
        Returns:
            热门关键词列表
        """
        try:
            logging.info(f"获取热门搜索关键词: days={days}, limit={limit}")

            sql = f"""
                SELECT keyword, COUNT(*) as search_count, 
                       SUM(count) as total_results
                FROM {self.table_name} 
                WHERE create_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                GROUP BY keyword
                ORDER BY search_count DESC, total_results DESC
                LIMIT %s
            """
            params = (days, limit)

            results = self.mysqlDB.execute_query(sql, params)

            popular_keywords = []
            for row in results:
                popular_keywords.append({
                    "keyword": row['keyword'],
                    "search_count": row['search_count'],
                    "total_results": row['total_results']
                })

            return {
                "code": 200,
                "message": "获取热门关键词成功",
                "data": popular_keywords
            }

        except Exception as e:
            logging.error(f"获取热门关键词失败: {str(e)}")
            return {"code": 500, "message": f"获取热门关键词失败: {str(e)}", "data": None}

    def delete_search_record(self, record_id: int, user_id: int = None) -> Dict:
        """
        删除搜索记录
        Args:
            record_id: 记录ID
            user_id: 用户ID (可选，如果提供则验证记录属于该用户)
        Returns:
            删除结果
        """
        try:
            logging.info(f"删除搜索记录: record_id={record_id}, user_id={user_id}")

            if user_id:
                # 验证记录属于该用户
                check_sql = f"SELECT id FROM {self.table_name} WHERE id = %s AND user_id = %s"
                check_result = self.mysqlDB.execute_query(check_sql, (record_id, user_id))

                if not check_result:
                    return {"code": 404, "message": "搜索记录不存在或无权删除", "data": None}

            # 删除记录
            if user_id:
                delete_sql = f"DELETE FROM {self.table_name} WHERE id = %s AND user_id = %s"
                delete_params = (record_id, user_id)
            else:
                delete_sql = f"DELETE FROM {self.table_name} WHERE id = %s"
                delete_params = (record_id,)

            result = self.mysqlDB.execute_update(delete_sql, delete_params)

            if result:
                logging.info(f"搜索记录删除成功: record_id={record_id}")
                return {"code": 200, "message": "搜索记录删除成功", "data": None}
            else:
                return {"code": 404, "message": "搜索记录不存在", "data": None}

        except Exception as e:
            logging.error(f"删除搜索记录失败: {str(e)}")
            return {"code": 500, "message": f"删除搜索记录失败: {str(e)}", "data": None}

    def clear_user_search_history(self, user_id: int) -> Dict:
        """
        清空用户搜索历史
        Args:
            user_id: 用户ID
        Returns:
            清空结果
        """
        try:
            logging.info(f"清空用户搜索历史: user_id={user_id}")

            sql = f"DELETE FROM {self.table_name} WHERE user_id = %s"
            params = (user_id,)

            result = self.mysqlDB.execute_update(sql, params)

            logging.info(f"用户搜索历史清空成功: user_id={user_id}, 删除记录数={result}")
            return {
                "code": 200,
                "message": "搜索历史清空成功",
                "data": {"deleted_count": result}
            }

        except Exception as e:
            logging.error(f"清空搜索历史失败: {str(e)}")
            return {"code": 500, "message": f"清空搜索历史失败: {str(e)}", "data": None}

    def get_search_statistics(self, user_id: int = None, days: int = 7) -> Dict:
        """
        获取搜索统计信息
        Args:
            user_id: 用户ID (可选，不提供则统计所有用户)
            days: 统计天数
        Returns:
            统计信息
        """
        try:
            logging.info(f"获取搜索统计: user_id={user_id}, days={days}")

            # 构建查询条件
            where_conditions = ["create_time >= DATE_SUB(NOW(), INTERVAL %s DAY)"]
            params = [days]

            if user_id:
                where_conditions.append("user_id = %s")
                params.append(user_id)

            where_clause = " AND ".join(where_conditions)

            sql = f"""
                SELECT 
                    COUNT(*) as total_searches,
                    COUNT(DISTINCT keyword) as unique_keywords,
                    COUNT(DISTINCT user_id) as active_users,
                    AVG(count) as avg_results,
                    MAX(create_time) as latest_search
                FROM {self.table_name} 
                WHERE {where_clause}
            """

            result = self.mysqlDB.execute_query(sql, tuple(params))

            if result:
                stats = result[0]
                return {
                    "code": 200,
                    "message": "获取统计信息成功",
                    "data": {
                        "total_searches": stats['total_searches'],
                        "unique_keywords": stats['unique_keywords'],
                        "active_users": stats['active_users'],
                        "avg_results": float(stats['avg_results']) if stats['avg_results'] else 0,
                        "latest_search": stats['latest_search'].strftime("%Y-%m-%d %H:%M:%S") if stats[
                            'latest_search'] else None
                    }
                }
            else:
                return {"code": 404, "message": "未找到统计信息", "data": None}

        except Exception as e:
            logging.error(f"获取搜索统计失败: {str(e)}")
            return {"code": 500, "message": f"获取搜索统计失败: {str(e)}", "data": None}